package com.pz.cloud.framework.util;

import com.baomidou.mybatisplus.annotation.TableField;
import com.pz.cloud.framework.base.annotation.Select;
import com.pz.cloud.framework.base.expection.CustomRuntimeException;
import org.apache.commons.lang3.StringUtils;
import org.jeecg.common.util.BeanConvertUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.persistence.Column;
import javax.persistence.Query;
import javax.persistence.Table;
import javax.persistence.Transient;
import java.lang.reflect.Field;
import java.util.*;

import static org.apache.commons.lang3.StringUtils.isBlank;

/**
 * @author shili.huang
 * @date 2019/9/11
 */
public class SqlUtil {

    private static final String ID_FIELD_NAME = "id";

    private static final Logger LOG = LoggerFactory.getLogger(SqlUtil.class);


    public static String listSql(Object entity){
        StringBuffer sql = new StringBuffer("select * from ");
        sql.append(entity.getClass().getAnnotation(Table.class).name()).append(" where 1=1");
        String result = sql.append(joiningQueryConditions(entity)).toString();
        LOG.info(result);
        return result;
    }
    public static String countSql(Object entity){
        StringBuffer sql = new StringBuffer("select count(1) from ");
        sql.append(entity.getClass().getAnnotation(Table.class).name()).append(" where 1=1");
        String result = sql.append(joiningQueryConditions(entity)).toString();
        LOG.info(result);
        return result;
    }

    public static String countSql(String sql){
        int from = sql.indexOf("from");
        String countSql = "select count(1) "+sql.substring(from);
        return countSql;
    }

    public static String joiningQueryConditions(Object entity){
        return joiningQueryConditions(entity,null);
    }

    public static String joiningQueryConditions(Object entity,String alias){
        StringBuilder sql = new StringBuilder();
        return joiningQueryConditions(sql,entity,alias);
    }
    public static String joiningQueryConditions(StringBuilder sql, Object entity, String alias){
        Field[] fields = FieldUtil.getAllFields(entity);
        String tableAlias = alias == null ? "" : alias + ".";
        Arrays.stream(fields).filter(field -> !field.isAnnotationPresent(Transient.class) || field.isAnnotationPresent(Select.class)).forEach(field -> {
            field.setAccessible(true);
            try {
                Object value = field.get(entity) instanceof String && isBlank((String)field.get(entity)) ? null : field.get(entity);
                if (value != null){
                    Select select = field.getAnnotation(Select.class);
                    Column column = field.isAnnotationPresent(Column.class) ? field.getAnnotation(Column.class) : null;
                    String fileName = column == null || StringUtils.isEmpty(column.name())? field.getName() : column.name();
                    if (select != null){
                        sql.append(" and ").append(select.expression());
                    }else if(value instanceof String){
                        sql.append(" and ").append(tableAlias).append(fileName).append(" like :").append(field.getName());
                    }else{
                        sql.append(" and ").append(tableAlias).append(fileName).append(" = :").append(field.getName());
                    }
                }
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
        });
        return sql.toString();
    }

    public static String createUpdateSql(Object entity,Map<String,Object> parms){
        Field[] fields = FieldUtil.getAllFields(entity);
        StringBuffer sql = new StringBuffer("update ");
        String tableName = BizUtil.getTableName(entity.getClass());
        sql.append(tableName).append(" set ");
        String idFieldName = BizUtil.getIdFieldName(entity);
        String id = BizUtil.getIdFieldValue(entity);
        if(StringUtils.isEmpty(id)){
            throw new CustomRuntimeException("id不能为空！");
        }
        List ignoreField = Arrays.asList("delFlag",idFieldName);

        for (Field field : fields){
            TableField tableField = field.getAnnotation(TableField.class);
            if((tableField==null||tableField.exist())&&!field.isAnnotationPresent(Transient.class)){
                field.setAccessible(true);
                try {
                    if (!ignoreField.contains(field.getName())){
                        Object value = field.get(entity);
                        String columnName = BeanConvertUtil.camelToUnderline(field.getName());
                        if(tableField!=null&&StringUtils.isNotEmpty(tableField.value())){
                            columnName = tableField.value();
                        }
                        sql.append(columnName).append(" = ").append(":").append(field.getName()).append(",");

                        if("updateTime".equals(field.getName())){
                            value = new Date();
                        }
                        parms.put(field.getName(),value);
                    }
                } catch (IllegalAccessException e) {
                   throw new RuntimeException(e);
                }
            }
        }
        String result = sql.deleteCharAt(sql.length() - 1).append(" where ").append(idFieldName).append("= :").append(idFieldName).toString();
        parms.put(idFieldName,id);

        LOG.info(result);
        return result;
    }

    public static String createInsertSql(Object entity){
        Field[] fields = FieldUtil.getAllFields(entity);
        StringBuffer fieldNames = new StringBuffer("");
        StringBuffer fieldValues = new StringBuffer("");
        StringBuffer sql = new StringBuffer("insert into ");
        sql.append(entity.getClass().getAnnotation(Table.class).name()).append("(");
        Arrays.stream(fields).filter(field -> !field.isAnnotationPresent(Transient.class)).forEach(field -> {
            field.setAccessible(true);
            try {
                Object value = field.get(entity);
                if (value != null){
                    Column column = field.isAnnotationPresent(Column.class) ? field.getAnnotation(Column.class) : null;
                    String fileName = column == null || StringUtils.isEmpty(column.name())? field.getName() : column.name();
                    fieldNames.append(fileName).append(",");
                    fieldValues.append(":").append(field.getName()).append(",");
                }
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
        });
        String result = sql.append(fieldNames.deleteCharAt(fieldNames.length() - 1)).append(")").append(" values (").append(fieldValues.deleteCharAt(fieldValues.length() - 1)).append(")").toString();
        LOG.info(result);
        return result;
    }

    public static void setParameter(Query query, Object entity){
        Field[] fields = FieldUtil.getAllFields(entity);
        Arrays.stream(fields).filter(field -> !field.isAnnotationPresent(Transient.class)).forEach(field -> {
            field.setAccessible(true);
            try {
                Object value = field.get(entity);
                if (value != null){
                    query.setParameter((String)field.getName(),value);
                }
            } catch (IllegalAccessException e) {
                LOG.error(e.getMessage());
            }
        });
    }

    public static void setQueryParameter(Query query, Object entity){
        Field[] fields = FieldUtil.getAllFields(entity);
        Arrays.stream(fields).filter(field -> !field.isAnnotationPresent(Transient.class) || field.isAnnotationPresent(Select.class)).forEach(field -> {
            field.setAccessible(true);
            try {
                Select select = field.getAnnotation(Select.class);
                String prefix = select == null ? "%" : select.prefix();
                String suffix = select == null ? "%" : select.suffix();
                Object value = field.get(entity) instanceof String && isBlank((String)field.get(entity)) ? null : field.get(entity);
                if (value != null){
                    if (value instanceof String){
                        query.setParameter((String)field.getName(),prefix + value + suffix);
                    }else {
                        query.setParameter((String)field.getName(),value);
                    }
                }
            } catch (IllegalAccessException e) {
                LOG.error(e.getMessage());
            }
        });
    }

    public static Map<String,Object> getQueryParam( Object entity){
        Field[] fields = FieldUtil.getAllFields(entity);
        Map params = new HashMap();
        Arrays.stream(fields).filter(field -> !field.isAnnotationPresent(Transient.class) || field.isAnnotationPresent(Select.class)).forEach(field -> {
            field.setAccessible(true);
            try {
                Select select = field.getAnnotation(Select.class);
                String prefix = select == null ? "%" : select.prefix();
                String suffix = select == null ? "%" : select.suffix();
                Object value = field.get(entity) instanceof String && isBlank((String)field.get(entity)) ? null : field.get(entity);
                if (value != null){
                    if (value instanceof String){
                        params.put(field.getName(),prefix + value + suffix);
                    }else {
                        params.put(field.getName(),value);
                    }
                }
            } catch (IllegalAccessException e) {
                LOG.error(e.getMessage());
            }
        });
        return params;
    }


    /**
     * 设置查询条件到query中
     *
     * @param params
     * @param query
     */
    public static void setQueryParams(List<Object> params, Query query) {
        for (int i = 0; i < params.size(); i++) {
            query.setParameter(i + 1, params.get(i));
        }
    }

    /**
     * 设置查询条件到query中
     * @param map
     * @param query
     */
    public static void setQueryParams(Map<String, Object> map, Query query) {
        for (String key : map.keySet()) {
            query.setParameter(key, map.get(key));
        }
    }

    /**
     *
     * @param start 分页开始行数
     * @param pageSize 每页显示记录总数
     * @param query
     */
    public static void setPageInfo(int start , int pageSize , Query query){
        query.setFirstResult(start);
        query.setMaxResults(pageSize);
    }

    /**
     * 获取页码,从0页开始
     * @return
     */
    public static int getPage(int start,int pageSize){
        return (int)Math.floor((double)start/pageSize);
    }

}
